package com.hotelManager.www.dao;

import com.hotelManager.www.po.BrowseHistory;
import com.hotelManager.www.po.User;
import com.hotelManager.www.po.Shop;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * 浏览历史DAO类
 * 实现浏览历史相关数据库操作
 */
public class BrowseHistoryDao {
    
    /**
     * 插入浏览历史记录时，手动验证 user_id 和 shop_id 的有效性。
     * @param conn 数据库连接
     * @param history 浏览历史记录
     * @return 插入是否成功
     */
    public boolean insert(Connection conn, BrowseHistory history) throws SQLException {
        // 验证 user_id 是否存在
        String userCheckSql = "SELECT COUNT(*) FROM users WHERE id = ?";
        try (PreparedStatement userCheckStmt = conn.prepareStatement(userCheckSql)) {
            userCheckStmt.setInt(1, history.getUserId());
            try (ResultSet rs = userCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("用户ID不存在: " + history.getUserId());
                }
            }
        }

        // 验证 shop_id 是否存在
        String shopCheckSql = "SELECT COUNT(*) FROM shop WHERE id = ?";
        try (PreparedStatement shopCheckStmt = conn.prepareStatement(shopCheckSql)) {
            shopCheckStmt.setInt(1, history.getShopId());
            try (ResultSet rs = shopCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("店铺ID不存在: " + history.getShopId());
                }
            }
        }

        // 插入浏览历史记录
        String sql = "INSERT INTO browse_history (user_id, shop_id, browse_time) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, history.getUserId());
            ps.setInt(2, history.getShopId());
            ps.setTimestamp(3, history.getBrowseTime());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 获取用户浏览历史
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 浏览历史列表
     */
    public List<BrowseHistory> getBrowseHistoryByUserId(Connection conn, int userId) throws SQLException {
        String sql = "SELECT * FROM browse_history WHERE user_id = ? ORDER BY browse_time DESC";
        List<BrowseHistory> histories = new ArrayList<>();
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                BrowseHistory history = new BrowseHistory();
                history.setId(rs.getInt("id"));
                history.setUserId(rs.getInt("user_id"));
                history.setShopId(rs.getInt("shop_id"));
                history.setBrowseTime(rs.getTimestamp("browse_time"));
                histories.add(history);
            }
        }
        
        return histories;
    }

    /**
     * 删除用户浏览历史
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 删除是否成功
     */
    public boolean deleteByUserId(Connection conn, int userId) throws SQLException {
        String sql = "DELETE FROM browse_history WHERE user_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 获取用户最近浏览的店铺
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param limit 限制数量
     * @return 店铺ID列表
     */
    public List<Integer> getRecentBrowsedShops(Connection conn, int userId, int limit) throws SQLException {
        String sql = "SELECT DISTINCT shop_id FROM browse_history WHERE user_id = ? ORDER BY browse_time DESC LIMIT ?";
        List<Integer> shopIds = new ArrayList<>();
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, limit);
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                shopIds.add(rs.getInt("shop_id"));
            }
        }
        
        return shopIds;
    }

    /**
     * 查询用户的详细浏览历史记录
     * 通过联表查询 browse_history、users 和 shop 表，获取用户的浏览历史，
     * 包括浏览时间、用户名和店铺信息。
     * 
     * @param userId 用户 ID
     * @return 包含详细信息的浏览历史列表
     * @throws SQLException 如果数据库操作失败
     */
    public List<BrowseHistory> findBrowseHistoryWithDetails(Connection conn,Integer userId) throws SQLException {
        // SQL 联表查询语句
        String sql = "SELECT bh.id, bh.browse_time, u.username, s.shopname, s.status " +
                     "FROM browse_history bh " +
                     "INNER JOIN users u ON bh.user_id = u.id " +
                     "INNER JOIN shop s ON bh.shop_id = s.id " +
                     "WHERE bh.user_id = ?";

        List<BrowseHistory> historyList = new ArrayList<>();

        try (
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userId); // 设置查询条件：用户 ID

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    // 创建 BrowseHistory 对象并填充数据
                    BrowseHistory history = new BrowseHistory();
                    history.setId(rs.getInt("id"));
                    history.setBrowseTime(rs.getTimestamp("browse_time"));

                    // 创建 User 对象并填充数据
                    User user = new User();
                    user.setUsername(rs.getString("username"));
                    history.setUser(user);

                    // 创建 Shop 对象并填充数据
                    Shop shop = new Shop();
                    shop.setShopname(rs.getString("shopname"));
                    shop.setStatus(rs.getString("status"));
                    history.setShop(shop);

                    // 将记录添加到结果列表
                    historyList.add(history);
                }
            }
        }

        return historyList;
    }
}